﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data.SqlClient;
using System.Data;
using System.Data.Common;

using Data_Objects;
using Business_Objects;
namespace Data_Objects.Implementation
{
    //Ko public -> tất cả gọi qua dataacess
    class SqlServerStudentDao : IStudentDao
    {
        public SqlServerStudentDao()
        {
            SQLUtility.TableName = "Student";
        }

        //Trả về đối tượng báo ăn
        private Student MakeStudent(DataRow row)
        {
            //con.Open();
            //SqlCommand cm = new SqlCommand("insert into nhasanxuat(logo,tennhasanxuat,manhasanxuat,website)values(@Pic,@tennsx,@mansx,@web)", con);

            //MemoryStream stream = new MemoryStream();
            //ptlogo.Image.Save(stream, System.Drawing.Imaging.ImageFormat.Jpeg);

            //byte[] pic = stream.ToArray();
            //cm.Parameters.AddWithValue("@Pic", pic);
            //cm.Parameters.AddWithValue("@tennsx", txttennhasanxuat.Text);

            //cm.ExecuteNonQuery();
            //MessageBox.Show("Thêm thành công...!", "Thông báo!");
            //___________________________
            //DataRow myRow;
            //myRow = ds.Tables["hinh"].Rows[0];
            //MyData = (byte[])myRow["logo"];
            //MemoryStream stream = new MemoryStream(MyData);
            //ptlogo.Image = Image.FromStream(stream);

            Guid _id = Guid.Parse(row["StudentID"].ToString());
            byte[] _image = (byte[])row["Image"];
            string _studentNumber = row["StudentNumber"].ToString();
            string _name = row["StudentName"].ToString();
            DateTime _studentBirthday = DateTime.Parse(row["StudentBirthday"].ToString());
            bool _sex = bool.Parse(row["Sex"].ToString());
            string _nationalities = row["Nationalities"].ToString();
            string _religion = row["Religion"].ToString();
            string _nationals = row["Nationals"].ToString();
            string _homeAddress = row["HomeAddress"].ToString();
            string _provinceHome = row["ProvinceHome"].ToString();
            string _districtHome = row["DistrictHome"].ToString();
            string _socialHome = row["SocialHome"].ToString();

            string _temporaryAddress = row["TemporaryAddress"].ToString();
            string _provinceTemporary = row["ProvinceTemporary"].ToString();
            string _districtTemporary = row["DistrictTemporary"].ToString();
            string _socialTemporary = row["SocialTemporary"].ToString();
            string _fatherName = row["FatherName"].ToString();
            string _fatherJob = row["FatherJob"].ToString();
            int _fatherMobile = int.Parse(row["FatherMobile"].ToString());
            string _motherName = row["MotherName"].ToString();
            string _motherJob = row["MotherJob"].ToString();
            int _motherMobile = int.Parse(row["MotherMobile"].ToString());

            string _communicantName = row["CommunicantName"].ToString();
            int _communicantMobile = int.Parse(row["CommunicantMobile"].ToString());
            string _communicantAddress = row["CommunicantAddress"].ToString();
            bool _martyrs = bool.Parse(row["Martyrs"].ToString());
            bool _wounded = bool.Parse(row["Wounded"].ToString());

            bool _illness = bool.Parse(row["Illness"].ToString());
            bool _revolution = bool.Parse(row["Revolution"].ToString());
            bool _poor = bool.Parse(row["Poor"].ToString());
            bool _orphaned = bool.Parse(row["Orphaned"].ToString());
            bool _unrefuge = bool.Parse(row["Unrefuge"].ToString());
            bool _disabilities = bool.Parse(row["Disabilities"].ToString());
            bool _minority = bool.Parse(row["Minority"].ToString());
            bool _inactive = bool.Parse(row["Inactive"].ToString());
            int _schoolID = int.Parse(row["SchoolID"].ToString());

            Guid _schoolYearID = Guid.Parse(row["SchoolYearID"].ToString());
            return new Student(_id, _image, _studentNumber, _name, _studentBirthday, _sex, _nationalities, _religion, _nationals, _homeAddress, _provinceHome, _districtHome, _socialHome, _temporaryAddress, _provinceTemporary, _districtTemporary, _socialTemporary, _fatherName, _fatherJob, _fatherMobile, _motherName, _motherJob, _motherMobile, _communicantName, _communicantMobile, _communicantAddress, _martyrs, _wounded, _illness, _revolution, _poor, _orphaned, _unrefuge, _disabilities, _minority, _inactive, _schoolID, _schoolYearID);
        }

        //Lấy danh sách khách hàng trả về datatable
        private IList<Student> MakeStudents(DataTable dt)  
        {
            IList<Student> list = new List<Student>();
            foreach (DataRow row in dt.Rows)
            {
                list.Add(MakeStudent(row));
            }
            return list;
        }
        private DbParameter[] Take(Student Student)
        {
            DbParameter[] parameters = new DbParameter[38];
            parameters[0] = new SqlParameter("@StudentID", Student.Id);
            parameters[1] = new SqlParameter("@Image", Student.Image);
            parameters[2] = new SqlParameter("@StudentNumber", Student.StudentNumber);
            parameters[3] = new SqlParameter("@StudentName", Student.Name);
            parameters[4] = new SqlParameter("@StudentBirthday", Student.StudentBirthday);

            parameters[5] = new SqlParameter("@Sex", Student.Sex);
            parameters[6]= new SqlParameter("@Nationalities", Student.Nationalities);
            parameters[7] = new SqlParameter("@Religion", Student.Religion);
            parameters[8] = new SqlParameter("@Nationals", Student.Nationals);
            parameters[9] = new SqlParameter("@HomeAddress", Student.HomeAddress);

            parameters[10] = new SqlParameter("@ProvinceHome", Student.ProvinceHome);
            parameters[11] = new SqlParameter("@DistrictHome", Student.DistrictHome);
            parameters[12] = new SqlParameter("@SocialHome", Student.SocialHome);
            parameters[13] = new SqlParameter("@TemporaryAddress", Student.TemporaryAddress);
            parameters[14] = new SqlParameter("@ProvinceTemporary", Student.ProvinceTemporary);

            parameters[15] = new SqlParameter("@DistrictTemporary", Student.DistrictTemporary);
            parameters[16] = new SqlParameter("@SocialTemporary", Student.SocialTemporary);
            parameters[17] = new SqlParameter("@FatherName", Student.FatherName);
            parameters[18] = new SqlParameter("@FatherJob", Student.FatherJob);
            parameters[19] = new SqlParameter("@FatherMobile", Student.FatherMobile);

            parameters[20] = new SqlParameter("@MotherName", Student.MotherName);
            parameters[21] = new SqlParameter("@MotherJob", Student.MotherJob);
            parameters[22] = new SqlParameter("@MotherMobile", Student.MotherMobile);
            parameters[23] = new SqlParameter("@CommunicantName", Student.CommunicantName);
            parameters[24] = new SqlParameter("@CommunicantMobile", Student.CommunicantMobile);

            parameters[25] = new SqlParameter("@CommunicantAddress", Student.CommunicantAddress);

            parameters[26] = new SqlParameter("@Martyrs", Student.Martyrs);
            parameters[27] = new SqlParameter("@Wounded", Student.Wounded);
            parameters[28] = new SqlParameter("@Illness", Student.Illness);
            parameters[29] = new SqlParameter("@Revolution", Student.Revolution);

            parameters[30] = new SqlParameter("@Poor", Student.Poor);
            parameters[31] = new SqlParameter("@Orphaned", Student.Orphaned);
            parameters[32] = new SqlParameter("@Unrefuge", Student.Unrefuge);
            parameters[33] = new SqlParameter("@Disabilities", Student.Disabilities);
            parameters[34] = new SqlParameter("@Minority", Student.Minority);

            parameters[35] = new SqlParameter("@Inactive", Student.Inactive);
            parameters[36] = new SqlParameter("@SchoolID", Student.SchoolID);
            parameters[37] = new SqlParameter("@SchoolYearID", Student.SchoolYearId);
            return parameters;
        }

        public IList<Business_Objects.Student> GetStudent()
        {
            SQLUtility.TableName = "Student";
            DbParameter[] param = null;
            DataTable dt = CommandProvider.GetDatatable(SQLUtility.ProceduresGetAll, param);
            return MakeStudents(dt);
        }

        public Business_Objects.Student GetStudentByID(string StudentID)
        {
            SQLUtility.TableName = "Student";
            DbParameter[] parameters = new DbParameter[1];
            parameters[0] = new SqlParameter("@StudentID", StudentID);
            DataRow row = CommandProvider.GetDataRow(SQLUtility.ProceduresGetByID, parameters);
            if (row == null) { return null; }
            else
            {
                return MakeStudent(row);
            }
        }

        public void InsertStudent(Business_Objects.Student Student)
        {
            SQLUtility.TableName = "Student";
            int newID = CommandProvider.Insert(SQLUtility.ProceduresInsert, Take(Student), true);
        }

        public int UpdateStudent(Business_Objects.Student Student)
        {
            SQLUtility.TableName = "Student";
            try { return CommandProvider.Update(SQLUtility.ProceduresUpdate, Take(Student)); }
            catch { return -1; }
        }

        public int DeleteStudent(Business_Objects.Student Student)
        {
            SQLUtility.TableName = "Student";
            try { return CommandProvider.Delete(SQLUtility.ProceduresDelete, Take(Student)); }
            catch { return -1; }
        }


        public List<Student> GetStudentBySchoolId(int schoolId)
        {
            DbParameter[] parameters = new DbParameter[1];
            parameters[0] = new SqlParameter("@SchoolID", schoolId);
            DataTable dt = CommandProvider.GetDatatable("usp_SelectStudentsByAndSchoolID", parameters);
            return MakeStudents(dt).ToList(); 
  
        }


        public List<Student> GetStudentByClassIdAndSchoolID(int schoolId, string classId) 
        {
            DbParameter[] parameters = new DbParameter[2];
            parameters[0] = new SqlParameter("@SchoolID", schoolId);
            parameters[1] = new SqlParameter("@ClassID", classId);
            DataTable dt = CommandProvider.GetDatatable("usp_SelectStudentsByAndSchoolIDAndClassID", parameters);
            return MakeStudents(dt).ToList(); 
  
        }


        public List<Student> GetStudentBySchoolIdAndSchoolYear(string SchoolYearID, int schoolId)
        {
            DbParameter[] parameters = new DbParameter[2];
            parameters[0] = new SqlParameter("@SchoolID", schoolId);
            parameters[1] = new SqlParameter("@SchoolYearID", SchoolYearID);
            DataTable dt = CommandProvider.GetDatatable("usp_SelectStudentsByAndSchoolIDAndSchoolYearID", parameters);
            return MakeStudents(dt).ToList(); 
  
        }


        public List<Student> GetStudentBySchoolIdAndSchoolYearNotClass(string SchoolYearID, int schoolId)
        {
            DbParameter[] parameters = new DbParameter[2];
            parameters[0] = new SqlParameter("@SchoolID", schoolId);
            parameters[1] = new SqlParameter("@SchoolYearID", SchoolYearID);
            DataTable dt = CommandProvider.GetDatatable("usp_SelectStudentsByAndSchoolIDAndSchoolYearIDNotInClass", parameters);
            return MakeStudents(dt).ToList(); 
        }
        public List<Student> GetStudentBySchoolIdAndSchoolYearClass(string SchoolYearID, int schoolId,string classId)
        {
            DbParameter[] parameters = new DbParameter[3];
            parameters[0] = new SqlParameter("@SchoolID", schoolId);
            parameters[1] = new SqlParameter("@SchoolYearID", SchoolYearID);
            parameters[2] = new SqlParameter("@ClassID", classId);
            DataTable dt = CommandProvider.GetDatatable("usp_SelectStudentsByAndSchoolIDAndSchoolYearIDInClass", parameters);
            return MakeStudents(dt).ToList();
        }
    }
}
